package com.softer.chengxin.traning.dao.provider;

import com.softer.chengxin.traning.entity.TraningInfo;
import org.apache.commons.lang3.StringUtils;

import java.util.Map;

/**
 * @author Gong Zhiyang
 * @date 2018/10/13
 */
public class TraningInfoProvider {
    /**
     * @param map
     * @return
     */
    public String selectByproject(Map<String, Object> map) {
        StringBuilder sql = new StringBuilder(20);
        String projectId = (String) map.get("projectId");
        sql.append("SELECT * FROM company_person where 1=1");
        if (StringUtils.isNotBlank(projectId)) {
            sql.append(" AND `project_id`=#{projectId}");
        }
        return sql.toString();
    }

    /**
     * 通过登陆人查询列表
     *
     * @param map
     * @return
     */
    public String getListByArea(Map<String, Object> map) {
        StringBuilder sql = new StringBuilder(20);
        TraningInfo traingInfo = (TraningInfo) map.get("traningInfo");
        sql.append("select t.id, IFNULL(d.num,0) as num,base.`name` as companyName,person.`name`,p.project_name as projectName ,dic.name AS educationLevel,");
        sql.append(" '项目经理' as titleName from traning_info as t ");
        sql.append(" left join company_base_info as base  on base.id=t.company_id ");
        sql.append(" left join company_project as p on p.id=t.project_id");
        sql.append(" left join company_person as person on person.id=t.person_id");
        sql.append(" left join dic_info dic on dic.code=person.education_level");
        sql.append(" left join (select count(1) as num,traning_id from traning_detail where state='2' GROUP BY traning_id)as d on d.traning_id=t.id");
        sql.append(" JOIN ( SELECT traning_id FROM traning_detail ");
        sql.append(" WHERE state = '1' AND FIND_IN_SET(edu_training_dep,'").append(traingInfo.getEducationLevel()).append("') GROUP BY traning_id ) AS d1 ON d1.traning_id = t.id");
        sql.append(" where t.state='1' ");
        commonSql(sql, traingInfo);
        return sql.toString();
    }


    /**
     * @param map
     * @return
     */

    public String selectAllTraning(Map<String, Object> map) {
        StringBuilder sql = new StringBuilder(20);
        TraningInfo traningInfo = (TraningInfo) map.get("traningInfo");
        sql.append("SELECT IFNULL(d.num,0) as num,t.id, p.project_name AS projectName,base.`name` AS companyName,");
        sql.append(" person.`name`,dic.name AS educationLevel,'项目经理' AS titleName");
        sql.append(" FROM `traning_info` AS t LEFT JOIN company_person AS person ON person.id = t.person_id");
        sql.append(" LEFT JOIN company_base_info AS base ON base.id = person.company_id");
        sql.append(" LEFT JOIN dic_info dic on dic.code=person.education_level");
        sql.append(" LEFT JOIN company_project AS p ON p.id = t.project_id ");
        sql.append(" left join (select count(1) as num,traning_id from traning_detail where state='2' group by traning_id )as d on d.traning_id=t.id");
        sql.append(" where 1=1");
        commonSql(sql, traningInfo);
        return sql.toString();
    }

    public String addTraning(Map<String, Object> map) {
        StringBuilder sql = new StringBuilder(20);
        TraningInfo traingInfo = (TraningInfo) map.get("traingInfo");
        sql.append(" insert into traning_info  ");
        commonSql(sql, traingInfo);
        return sql.toString();
    }


    private void commonSql(StringBuilder sql, TraningInfo traningInfo) {
        if (StringUtils.isNotBlank(traningInfo.getArea())) {
            sql.append(" and IF(t.project_id=0, (CASE base.community WHEN 0 THEN FIND_IN_SET(base.street,'").append(traningInfo.getArea()).append("') ELSE FIND_IN_SET( base.community,'").append(traningInfo.getArea()).append("') end),");
            sql.append("(CASE p.community WHEN 0 THEN FIND_IN_SET(p.street,'").append(traningInfo.getArea()).append("') ELSE FIND_IN_SET(p.community,'").append(traningInfo.getArea()).append("') end))");
        }
        if (StringUtils.isNotBlank(traningInfo.getCompanyId())) {
            sql.append(" AND t.company_id=#{traningInfo.companyId}");
        }
        if (StringUtils.isNotBlank(traningInfo.getCompanyName())) {
            sql.append(" and INSTR(base.name,#{traningInfo.companyName})>0 ");
        }
        if (StringUtils.isNotBlank(traningInfo.getProjectName()) ) {
            sql.append(" and INSTR(p.project_name,#{traningInfo.projectName})>0 ");
        }
        if ( StringUtils.isNotBlank(traningInfo.getName()) ) {
            sql.append(" and INSTR(person.name,#{traningInfo.name})>0 ");
        }
    }

    /**
     * 通过登陆人查询列表
     *
     * @param map
     * @return
     */
    public String selectStateByArea(Map<String, Object> map) {
        StringBuilder sql = new StringBuilder(20);
        TraningInfo traningInfo = (TraningInfo) map.get("traningInfo");
        sql.append("SELECT t.file_id AS fileId, IFNULL(n.num, 0) AS num, '项目经理' AS titleName,");
        sql.append("dic.name AS educationLevel,t.id,p.project_name as projectName,");
        sql.append("base.`name` AS companyName,person.`name`,p.project_name");
        sql.append(" FROM traning_info AS t");
        sql.append(" LEFT JOIN(SELECT traning_id,COUNT(1) as num FROM traning_detail WHERE state!=0 GROUP BY traning_id)d ON t.id = d.traning_id");
        sql.append(" LEFT JOIN company_base_info AS base ON base.id = t.company_id");
        sql.append(" LEFT JOIN company_project AS p ON p.id = t.project_id");
        sql.append(" LEFT JOIN company_person AS person ON person.id = t.person_id");
        sql.append(" LEFT JOIN dic_info dic on dic.code=person.education_level");
        sql.append(" LEFT JOIN ( SELECT count(1) AS num, traning_id FROM traning_detail WHERE state = '2' GROUP BY traning_id ) AS n ON n.traning_id = t.id");
        sql.append(" WHERE d.num>0");
        commonSql(sql, traningInfo);
        sql.append(" ORDER BY companyName DESC,project_name DESC");
        return sql.toString();
    }
}
